﻿Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.IO
Imports System.Data
Imports System.Drawing
Partial Class WebForm_ManageWorkRecord_ImportHour
    Inherits System.Web.UI.UserControl

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        ScriptManager.GetCurrent(Me.Page).RegisterPostBackControl(Me.btnImportData)

    End Sub
    Protected Sub btnImportData_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnImportData.Click

        If Not FileUpload2.HasFile Then
            MyObj.ShowMessage("請選擇上傳的檔案")
            Return
        ElseIf IO.Path.GetExtension(FileUpload2.PostedFile.FileName).ToUpper() <> ".XLS" AndAlso IO.Path.GetExtension(FileUpload2.PostedFile.FileName).ToUpper() <> ".XLSX" Then
            MyObj.ShowMessage("匯入失敗!!請選擇副檔名(xls、xlsx)檔案!!")
            Return
        End If

        '上傳暫存檔
        Dim clsRandom As New clsRandom()
        Dim newName As String = Server.MapPath("~/") & "/tmp/" & clsRandom.Generate() & IO.Path.GetExtension(FileUpload2.PostedFile.FileName).ToUpper()
        FileUpload2.PostedFile.SaveAs(newName)

        Dim ExcelImportCheck As New ExcelImportCheck()
        Dim dt As New DataTable

        '檢查工作表名稱
        If ExcelImportCheck.LoadData(dt, newName, "VolunteerList") = False Then
            Me.gv.Visible = False
            Return
        End If


        Dim ColumnList As String(,) = {{"身份證號碼", "N"}, {"姓名", "N"}, {"紀錄冊號碼", "N"}, {"發冊日期", "N"}}
        If ExcelImportCheck.CheckColumn(dt, ColumnList) = False Then
            Me.gv.Visible = False
            Return
        End If

        Dim sql As New MyStringBuilder
        Dim dat As New clsData
        '取出志工資料
        '==============================================
        sql.Clear()
        Dim VolunteerDT, VolunteerTempDT As Data.DataTable
        Dim VolunteerTempView As DataView

        sql.AppendFormat("Select replace(replace(left(VolunteerID,3)+REPLICATE('X',3)+right(VolunteerID,4)+VolunteerName, '  ', ''), ' ', '')  As VolunteerData from Volunteer; ")
        VolunteerDT = dat.GetData(sql)
        '==============================================


        ' 取出志工証資料
        '==============================================
        'sql.Clear()
        'Dim VBookDT, VBookTempDT As Data.DataTable
        'Dim VBookTempView As DataView

        'sql.AppendFormat(" Select  VolunteerID  From Vbook Where Active='Y'; ")
        'VBookDT = dat.GetData(sql)

        For j As Integer = 0 To dt.Columns.Count - 1
       

            If j >= 4 AndAlso j < 10 AndAlso Left(dt.Columns(j).ColumnName, 1) = "F" Then
                MyObj.ShowMessage("欄位名稱需為文字格式!")
                Me.gv.Visible = False
                Return
            Else
                dt.Columns(j).ColumnName = dt.Columns(j).ColumnName.Trim()
            End If
        Next

        '濾掉空白資料
        Dim FilterValue As String = ""
        For k As Integer = 0 To (ColumnList.Length / 2) - 1
            If FilterValue = "" Then
                FilterValue &= "[" & ColumnList(k, 0) & "]<>''"
            Else
                FilterValue &= " or [" & ColumnList(k, 0) & "]<>''"
            End If
        Next

        Dim MyDefaultView As Data.DataView = dt.DefaultView
        MyDefaultView.RowFilter = FilterValue
        dt = MyDefaultView.ToTable()
        '隱藏錯誤訊息gridview
        Me.gv.Visible = False
        Dim ErrorCount As Integer = 0

        Dim colString As DataColumn = New DataColumn("檢查結果")
        colString.DataType = System.Type.GetType("System.String")
        dt.Columns.Add(colString)

        Dim ColumnString As String = "Test,"
        Dim Year As Integer = 0
        For j As Integer = (ColumnList.Length / 2) To dt.Columns.Count - 2
            If ColumnString.Length = 8 Then
                ColumnString = Right(ColumnString, 3)
            End If

            If dt.Columns(j).ColumnName.Contains("年") Then
                MyObj.ShowMessage("匯入年份必須為數字!!!")
                Me.gv.Visible = False
                Return
            Else
                Year = Int32.Parse(dt.Columns(j).ColumnName) + 1911
                If (Year > 2009 Or Year < 2004) AndAlso ColumnString.Contains(IIf(dt.Columns(j).ColumnName.Length > 2, Left(dt.Columns(j).ColumnName, 2), dt.Columns(j).ColumnName)) = False Then
                    MyObj.ShowMessage("匯入年份不能大於98年或是小於93年!!")
                    Me.gv.Visible = False
                    Return
                ElseIf ColumnString.Contains(IIf(dt.Columns(j).ColumnName.Length > 2, Left(dt.Columns(j).ColumnName, 2), dt.Columns(j).ColumnName)) = True Then
                    MyObj.ShowMessage("匯入年份不能重覆!!")
                    Me.gv.Visible = False
                    Return
                Else
                    ColumnString &= dt.Columns(j).ColumnName & ","
                End If

            End If

        Next

        For i As Integer = 0 To dt.Rows.Count - 1


    


            If dt.Rows(i)("姓名").ToString().Trim() = "" OrElse dt.Rows(i)("身份證號碼").ToString().Trim() = "" Then
                dt.Rows(i)("檢查結果") &= "姓名及身分證請填寫完整,"
                ErrorCount = ErrorCount + 1
            Else
                VolunteerTempView = VolunteerDT.DefaultView
                VolunteerTempView.RowFilter = "VolunteerData='" + dt.Rows(i)("身份證號碼").ToString().Trim() + LTrim(dt.Rows(i)("姓名").ToString().Replace("  ", "").Replace(" ", "")) + "'"
                VolunteerTempDT = VolunteerTempView.ToTable()

                If VolunteerTempDT.Rows.Count = 0 Then
                    dt.Rows(i)("檢查結果") &= dt.Rows(i)("姓名").ToString() + "此志工的身份證號碼不存在,"
                    ErrorCount = ErrorCount + 1
                Else
                    If GetVolunteerID(dt.Rows(i)("身份證號碼").ToString().Trim(), dt.Rows(i)("姓名").ToString().Trim()) <> "" AndAlso GetVolunteerID(dt.Rows(i)("身份證號碼").ToString().Trim(), dt.Rows(i)("姓名").ToString().Trim()) <> "Error" Then
                        dt.Rows(i)("身份證號碼") = GetVolunteerID(dt.Rows(i)("身份證號碼").ToString().Trim(), dt.Rows(i)("姓名").ToString().Trim())


                        '檢查志工証
                        'VBookTempView = VBookDT.DefaultView
                        'VBookTempView.RowFilter = "VolunteerID='" + dt.Rows(i)("身份證號碼").ToString().Trim() + "'"
                        'VBookTempDT = VBookTempView.ToTable()

                        'If VBookTempDT.Rows.Count = 0 Then
                        '    dt.Rows(i)("檢查結果") &= dt.Rows(i)("姓名").ToString() + "沒有紀錄冊號碼存在,"
                        '    ErrorCount = ErrorCount + 1
                        'End If
                    ElseIf GetVolunteerID(dt.Rows(i)("身份證號碼").ToString().Trim(), dt.Rows(i)("姓名").ToString().Trim()) = "Error" Then
                        dt.Rows(i)("檢查結果") &= dt.Rows(i)("姓名").ToString() + "的身分證字號大於兩筆,"
                        ErrorCount = ErrorCount + 1
                    Else
                        dt.Rows(i)("檢查結果") &= dt.Rows(i)("姓名").ToString() + "的身分證字號無法比對,"
                        ErrorCount = ErrorCount + 1
                    End If

                End If


            End If



        Next


        '若以上無錯誤，開始匯入資料
        If ErrorCount = 0 Then
            Using scope As New Transactions.TransactionScope
                dt.Columns.Remove("檢查結果")
                For i As Integer = 0 To dt.Rows.Count - 1
                    For j As Integer = (ColumnList.Length / 2) To dt.Columns.Count - 1
                        sql.Length = 0
                        If dt.Rows(i)(dt.Columns(j).ColumnName).ToString() <> "" Then
                            sql.AppendFormat(" Delete From ImportWorkHourRecord Where VolunteerID='{0}' ", dt.Rows(i)("身份證號碼").ToString().Trim())
                            sql.AppendFormat(" And EffectiveDate='{0}' ", Int32.Parse(dt.Columns(j).ColumnName.Trim()) + 1911 & "/12/31")
                            sql.AppendFormat(" And StartDate = '{0}'; ", Int32.Parse(dt.Columns(j).ColumnName.Trim()) + 1911 & "/01/01")


                            '新增志工時數(環境清掃)
                            sql.AppendFormat(" Insert Into ImportWorkHourRecord(ItemType,VOrganizationID,VolunteerID,WorkHour,WorkTimes,StartDate,EffectiveDate,CreateDate,CreateBy,Active) Select ")
                            sql.AppendFormat(" '1' ")
                            sql.AppendFormat(",'{0}' ", "")
                            sql.AppendFormat(",'{0}' ", dt.Rows(i)("身份證號碼").ToString().Trim())
                            sql.AppendFormat(",'{0}' ", IIf(Not TypeOf (dt.Rows(i)(dt.Columns(j).ColumnName)) Is DBNull, dt.Rows(i)(dt.Columns(j).ColumnName).ToString().Trim(), 0))
                            sql.AppendFormat(",'1' ")
                            sql.AppendFormat(",'{0}' ", Int32.Parse(dt.Columns(j).ColumnName.Trim()) + 1911 & "/01/01")
                            sql.AppendFormat(",'{0}' ", Int32.Parse(dt.Columns(j).ColumnName.Trim()) + 1911 & "/12/31")
                            sql.AppendFormat(",getDate() ")
                            sql.AppendFormat(",'{0}' ", clsAuth.GetUser().UserID)
                            sql.AppendFormat(",'Y'; ")

                            dat.AccData(sql)

                            'MyObj.WriteLog(sql.ToString())

                        End If
                    Next

                Next

                scope.Complete()

                MyObj.ShowMessage("匯入成功!")
            End Using

        Else
            MyObj.ShowMessage("匯入失敗!")
            Me.gv.Visible = True
            For i As Integer = 0 To dt.Rows.Count - 1
                dt.Rows(i)("身份證號碼") = Left(dt.Rows(i)("身份證號碼").ToString(), 3) & New String("X", 3) & Right(dt.Rows(i)("身份證號碼").ToString(), 4)
                dt.Rows(i)("檢查結果") = dt.Rows(i)("檢查結果").ToString().TrimEnd(",")
            Next
            Me.gv.DataSource = dt
            Me.gv.DataBind()
        End If
    End Sub
    Public Function GetVolunteerID(ByVal VolunteerID As String, ByVal VolunteerName As String) As String
        Dim dat As New clsData
        Dim sql As New MyStringBuilder

        sql.Length = 0
        sql.AppendFormat("select  VolunteerID from Volunteer ")
        sql.AppendFormat("where left(VolunteerID,3)+REPLICATE('X',3)+right(VolunteerID,4) ='{0}' ", VolunteerID)
        sql.AppendFormat("AND ltrim(replace(replace(VolunteerName, '  ', ''), ' ', '')) = N'{0}' ", LTrim(VolunteerName.Replace("  ", "").Replace(" ", "")))
        '  MyObj.WriteLog(sql.ToString())
        If dat.GetData(sql).Rows.Count = 1 Then
            Return dat.GetData(sql).Rows(0)("VolunteerID").ToString()
        ElseIf dat.GetData(sql).Rows.Count > 1 Then
            Return "Error"
        Else
            Return ""
        End If
    End Function
End Class
